class QueryController < ApplicationController
	before_filter :login_required
	layout "framework"
	def index
		@session[:rights] = buildMenu '报表中心'
	end
	
	def agent_total
		if !request.get?
			@totalIn = SalePaperItem.find_by_sql "SELECT agents.name,sum(current_count) as count,sum(price*current_count) as price from sale_paper_items,sale_papers,agents where sale_paper_items.sale_paper_id=sale_papers.id and agent_id=agents.id and sale_date_at between '#{params[:from]}' and '#{params[:to]}' and (sale_papers.papertype='NDFD' or  sale_papers.papertype='RE') group by agent_id"
			#@totalOut = SalePaperItem.find_by_sql 'SELECT agents.name,sum(current_count),sum(price*current_count) from return_paper_items,sale_papers,agents where return_paper_items.sale_paper_id=sale_papers.id and agent_id=agents.id and sale_date_at between #{params[:from]} and #{params[:to]} group by agent_id'
		end
	end
	
	def agent_list
		@agents = Agent.find(:all,:order=>'id')
		if !request.get?
			agent_id = params[:agent_id]
			from = params[:from]
			to   = params[:to]
			condition = nil
			if from && from.length > 0
				condition = "select a.product_id,c.serial,c.name,a.sale_paper_id,a.current_count,b.sale_date_at,a.price,a.serial as serialp from Sale_Paper_Items a,sale_papers b,products c where a.product_id=c.id and a.sale_paper_id=b.id and b.sale_date_at between '#{from}' and '#{to}' and b.agent_id=#{agent_id} and (b.papertype='NDFD' or  b.papertype='RE')"
			else
                            condition = "select * from Sale_Paper_Items,sale_papers,products where Sale_Paper_Items.product_id=products.id and sale_paper_items.sale_paper_id=sale_papers.id and sale_papers.agent_id=#{agent_id} and (sale_papers.papertype='NDFD' or  sale_papers.papertype='RE')"
			end
			@agentList = SalePaperItem.find_by_sql(condition)
		end
	end
	
	def product_agent
            if !request.get?
                #product = Product.find_by_name(params[:name])
                product_a = Product.find_by_sql "SELECT * FROM products WHERE products.name='#{params[:name]}' and products.serial='#{params[:serial]}' LIMIT 1"
                if product_a !=[]
                    @productList = SalePaperItem.find_by_sql "select Sale_Paper_Items.serial as serialp,Sale_Paper_Items.product_id,agents.id as id,agents.name,sum(current_count) as count ,sale_papers.sale_date_at ,sale_paper_items.sale_paper_id as sale_paper_id from Sale_Paper_Items,sale_papers,agents where sale_papers.agent_id=agents.id and Sale_Paper_Items.product_id=#{product_a[0].id} and Sale_Paper_Items.sale_paper_id=sale_papers.id  and (sale_papers.papertype='NDFD' or sale_papers.papertype='RE' ) group by sale_papers.agent_id"
                    #retList = SalePaperItem.find_by_sql "select agents.name,sum(return_Paper_Items.current_count) as count from return_Paper_Items,sale_papers,agents where sale_papers.agent_id=agents.id and return_Paper_Items.product_id=#{product[0].id} and return_Paper_Items.sale_paper_id=sale_papers.id group by sale_papers.agent_id"
                    #@retHash = Hash.new
                    #retList.each do |ret|
                    #    @retHash[ret.name]=ret.count
                    #end
                    @productTotal = SalePaperItem.find_by_sql "select sum(sale_Paper_Items.current_count) as count from sale_Paper_Items,sale_papers where sale_Paper_Items.product_id=#{product_a[0].id} and sale_Paper_Items.sale_paper_id=sale_papers.id and (sale_papers.papertype='NDFD' or sale_papers.papertype='RE')"
                    @retTotal = SalePaperItem.find_by_sql "select sum(return_Paper_Items.current_count) as count from return_Paper_Items,sale_papers where return_Paper_Items.product_id=#{product_a[0].id} and return_Paper_Items.sale_paper_id=sale_papers.id"
                else
                    flash[:notice] = "<img src='/images/fail.gif'><font color='red'>#{params[:serial]}--#{params[:name]}产品不存在！</font>"
                    return

                end
            end
        end
	
	def code
		if !request.get?
			prefix = params[:prefix]
			postfix = params[:postfix]
			from = params[:from]
			to = params[:to]
			@codes = Code.find(:all,:conditions=>"prefix='#{prefix}' and postfix='#{postfix}' and code between #{from} and #{to}")
		end
	end
	
	def return_recorde
		if !request.get?
			product_r = nil
                        if params[:name].length != 0 || params[:serial].length !=0
                                product_r = Product.find(:first,:conditions=>"name like '%#{params[:name]}%' and serial like'%#{params[:serial]}%'") 
                        end
			from  = params[:from]
			to = params[:to]
			puts params[:name]
			conditions = ''
                            if product_r
                                conditions += "product_id=#{product_r.id}"
                            elsif from and to and !from.eql? '' and !to.eql? ''
                                conditions += "create_date_at between '#{from}' and '#{to}'"
                            end
                        
                            puts conditions
                            if !conditions.eql? ''
                                #@return_paper_items = ReturnPaperItem.find(:all,:conditions=>conditions)
                                @return_paper_item_pages, @return_paper_items = paginate(:return_paper_items,:conditions=>conditions,:order=>'create_date_at DESC',:per_page => 20)
                            else
                                @return_paper_item_pages, @return_paper_items = paginate(:return_paper_items,:order=>'create_date_at DESC',:per_page => 20)
			    end
		else
			@return_paper_item_pages, @return_paper_items = paginate(:return_paper_items,:order=>'create_date_at DESC',:per_page => 20)
		end
	end
	
        def store_move
                if request.get?
                        @store_transaction_pages, @store_transactions = paginate(:store_transactions,:order=>'create_date_at DESC',:per_page => 20)
                else
                        product_p = nil
                        from  = params[:from]
                        to = params[:to]
                        puts params[:name]
                        conditions = ''
                        if params[:name].length != 0 || params[:serial].length !=0
                                product_p = Product.find(:first,:conditions=>"name like '%#{params[:name]}%' and serial like'%#{params[:serial]}%'") 
                                if product_p
                                        conditions += "product_id=#{product_p.id} and create_date_at between '#{from}' and '#{to}'"
                                end
                        else 
                                conditions += "create_date_at between '#{from}' and '#{to}'"
                        end
                        if !conditions.eql? ''
                                #@store_transactions = StoreTransaction.find(:all,:conditions=>conditions)
                                @store_transaction_pages, @store_transactions = paginate(:store_transactions,:conditions=>conditions,:order=>'create_date_at DESC',:per_page => 20)
                        else
                                @store_transaction_pages, @store_transactions = paginate(:store_transactions,:order=>'create_date_at DESC',:per_page => 20)
                        end
                end
        end
	
	def employee_sale
            @employees = Employee.find(:all,:order=>'id')
            if !request.get?
                if params[:employee_id]==nil
                    return
                else
                    conditions="employee_id=#{params[:employee_id]} and sale_date_at between '#{params[:from]}' and '#{params[:to]}' and (papertype='NDFD' or papertype='RE')"
                    @sale_papers = SalePaper.find(:all,:conditions=>conditions,:order=>'sale_date_at DESC')
                    if @sale_papers != []
                        @total = SalePaperItem.find_by_sql("select SUM(current_count*price) as total from sale_paper_items WHERE sale_paper_items.sale_paper_id in (select id from sale_papers where #{conditions})")
                    end
                end
            end
        end
	
	def query_employee
		@departments = Department.find_all
	end
	
	def manage_agent
		if @session[:account].role.memo.include? 'salemanager'
			@agents = Agent.find(:all,:order=>'employee_id')
		else
			@agents = Agent.find(:all,:conditions=>"employee_id=#{@session[:account].employee.id}",:order=>'employee_id')
		end
	end

	def reconciliation
            @agents_1 = Agent.find(:all,:order=>'id')
		if !request.get?
                        if params[:agent_id] != '' && params[:get_date_at] != ''
                            conditions="a.agent_id=#{params[:agent_id]} and b.get_date_at like '%#{params[:get_date_at]}%'"
                            @sale_papers = SalePaper.find_by_sql("select a.id, b.get_date_at, b.serial as serialno, c.serial, b.price, b.current_count, b.status from sale_papers a , sale_paper_items b, products c where (a.papertype = 'RE' or a.papertype = 'NDFD') and a.id = b.sale_paper_id and b.product_id = c.id and #{conditions}")
                            @agents = Agent.find(:first,:conditions=>"id=#{params[:agent_id]}")
                            @company_info = ConfigVariable.find_all
                        else
                            return
                        end
		end
	end

	def goods_send
            @agents_1 = Agent.find(:all,:order=>'id')
		if !request.get?
                        if params[:agent_id] != ''
                            conditions="b.serial='#{params[:serial]}' and a.agent_id='#{params[:agent_id]}'"
                            @sale_papers = SalePaper.find_by_sql("select a.id, a.customer_book_no, c.serial, c.F, c.B, c.P, b.current_count, b.price, c.name, b.goods_num , b.memo from sale_papers a, sale_paper_items b, products c where a.id=b.sale_paper_id and a.papertype='NDFD' and c.id=b.product_id and #{conditions}")
                            @agents = Agent.find(:first,:conditions=>"id=#{params[:agent_id]}")
                            @company_info = ConfigVariable.find_all
                        else
                            return
                        end
		end
	end

end
